package dto;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import model.Category;

public class DtoCategory {
	private static List<Category> getAll(String sql, DataManager dataManager) {
		Connection con = dataManager.getConnection();
		List<Category> categories = new ArrayList<Category>();
		ResultSet rs = null;
		Statement statement = null;
		Category category = null;
		try {
			statement = con.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				category = new Category();
				category.setCategoryId(rs.getInt("categoryId"));
				category.setCategoryName(rs.getString("categoryName"));
				category.setCategoryParentId(rs.getInt("categoryParentId"));
				
				categories.add(category);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (statement != null)
					statement.close();
				dataManager.closeConnection(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return categories;

	}

	public static List<Category> getAll(DataManager dataManager) {
		String sql = "select * from  category";

		return getAll(sql, dataManager);
	}

	public static Category getById(int categoryId, DataManager dataManager) {
		String sql = "select * from category where categoryId = " + categoryId;
		List<Category> categories = getAll(sql, dataManager);
		return categories.isEmpty() ? null : categories.get(0);
	}
	
	public static Category getByName(String categoryName, DataManager dataManager)
	{
		String sql = "SELECT * FROM category WHERE categoryName = '"+categoryName + "'";
		List<Category> categories = getAll(sql, dataManager);
		return categories.isEmpty() ? null : categories.get(0);
	}
	
	public static List<Category> getByParentId(int categoryParentId, DataManager dataManager) {
		String sql = "select * from category where categoryParentId = " + categoryParentId;
		
		return getAll(sql, dataManager);
	}
	
	private static void excuteWithCategoryNonQuery(DataManager dataManager, String sqlQuery) {
		Connection con = dataManager.getConnection();
		Statement statement = null;
		
		try {
			statement = con.createStatement();
			statement.executeUpdate(sqlQuery);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (statement != null)
					statement.close();
				dataManager.closeConnection(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public static void updateCategoryWithCategoryId(Category category, DataManager dataManager) {
		String sqlQuery = "update Category set categoryName = \'" + category.getCategoryName() + "\', " +
						"categoryParentId = " + category.getCategoryParentId() + " where categoryId=" + category.getCategoryId();
		
		excuteWithCategoryNonQuery(dataManager, sqlQuery);
	}
	
	public static void deleteCategory(DataManager dataManager, int categoryId) {
		String sqlQuery = "delete from Category where categoryId=" + categoryId;
		
		excuteWithCategoryNonQuery(dataManager, sqlQuery);
	}
	
	public static void addCategory(DataManager dataManager, Category category) {
		String sqlQuery = "insert into Category (categoryName, categoryParentId) values ('" + category.getCategoryName() + 
							"', " + category.getCategoryParentId() + ")";
		
		excuteWithCategoryNonQuery(dataManager, sqlQuery);
	}
	
	public static void getTreeStructure(DataManager dataManager, List<String> categoryNames,
			List<Integer> categoryIds, int categoryParentId) {
		List<Category> categorys = getByParentId(categoryParentId, dataManager);
		
		getTreeStructureImpl(categorys, 0, categoryNames, categoryIds, dataManager);
	}

	private static void getTreeStructureImpl(List<Category> categorys, int level,
			List<String> categoryNames, List<Integer> categoryIds, DataManager dataManager) {
		Iterator<Category> iter = categorys.iterator();
		while (iter.hasNext()) {
			Category category = iter.next();
			StringBuffer sbuff = new StringBuffer();
			for (int i = 0; i < level; ++i)
				sbuff.append("    ");
			sbuff.append(category.getCategoryName());
			categoryNames.add(sbuff.toString());
			categoryIds.add(category.getCategoryId());
			List<Category> tempCategorys = getByParentId(category.getCategoryId(), dataManager);
			getTreeStructureImpl(tempCategorys, level + 1, categoryNames,
					categoryIds, dataManager);
		}
	}
}